﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Libraries.Common;
using Libraries.Entities;

namespace Libraries.Repositories
{
    public class CourseRepository
    {
        #region Course Details
        private CourseDetails ParseToEntityCourse(IDataRecord dataReader)
        {
            if (dataReader != null)
            {
                return new CourseDetails
                {
                    CourseId = dataReader["CourseId"].ToInt32OrDefault(),
                    CourseName = dataReader["CourseName"].ToStringOrDefault(),
                    CourseCategoryId = dataReader["CourseCategoryId"].ToInt32OrDefault(),
                    CourseCategory = dataReader["CourseCategory"].ToStringOrDefault(),
                    CourseCategoryName = dataReader["CourseCategoryName"].ToStringOrDefault(),
                    CourseYear = dataReader["CourseYear"].ToInt32OrDefault(),
                    Costs = (decimal?)dataReader["Costs"],
                    Currency = dataReader["CurrencyName"].ToStringOrDefault(),
                    TimeCommit = dataReader["TimeCommit"].ToInt32OrDefault(),
                    TrainedDuration = (decimal?)dataReader["TrainedDuration"],
                    Note = dataReader["Note"].ToStringOrDefault(),
                    EndDate = dataReader["EndDate"].ToDateTimeOrNull(),
                    StartDate = dataReader["StartDate"].ToDateTimeOrNull(),
                    TotalRecord = dataReader["TotalRow"].ToInt32OrDefault(),
                };
            }
            return null;
        }
        /// <summary>
        /// All course
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="currentpage"></param>
        /// <returns></returns>
        public Collection<CourseDetails> PagingAllCourse(int pageSize, int currentpage)
        {
            var parameters = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter("@PageSize",pageSize),
                                                          new SqlParameter("@CurrentPage",currentpage),
                                                      };

            Collection<CourseDetails> list = null;
            if (!string.IsNullOrEmpty(ProcedureConfiguration.GetAllCoursePage))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.GetAllCoursePage, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        list = new Collection<CourseDetails>();
                        while (result.Read())
                        {
                            list.Add(ParseToEntityCourse(result));
                        }
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// Get All Course by CategoryId
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="currentpage"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public Collection<CourseDetails> PagingAllCourseByCategoryId(int pageSize , int currentpage ,int categoryId)
        {
            var parameters = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter("@PageSize",pageSize),
                                                          new SqlParameter("@CurrentPage",currentpage),
                                                           new SqlParameter("@CourseCategoryId",categoryId),
                                                      };

            Collection<CourseDetails> list = null;
            if (!string.IsNullOrEmpty(ProcedureConfiguration.GetAllCoursePageByCategoryId))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.GetAllCoursePageByCategoryId, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        list = new Collection<CourseDetails>();
                        while (result.Read())
                        {
                            list.Add(ParseToEntityCourse(result));
                        }
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// Search Course By Category
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="currentpage"></param>
        /// <param name="categoryId"></param>
        /// <param name="courseName"></param>
        /// <param name="courseYear"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public Collection<CourseDetails> PagingSearchCourseByCategoryId(int pageSize, int currentpage, int categoryId, string courseName, int? courseYear, string startDate, string endDate)
        {
            var parameters = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter("@PageSize",pageSize),
                                                          new SqlParameter("@CurrentPage",currentpage),
                                                           new SqlParameter("@CourseCategoryId",categoryId),
                                                           new SqlParameter("@CourseName",courseName),
                                                           new SqlParameter("@CourseYear",courseYear == null?DBNull.Value:(object)courseYear),
                                                           new SqlParameter("@StartDate",startDate),
                                                           new SqlParameter("@EndDate",endDate),
                                                      };

            Collection<CourseDetails> list = null;
            if (!string.IsNullOrEmpty(ProcedureConfiguration.SearchCoursePageByCategoryId))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.SearchCoursePageByCategoryId, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        list = new Collection<CourseDetails>();
                        while (result.Read())
                        {
                            list.Add(ParseToEntityCourse(result));
                        }
                    }
                }
            }
            return list;
        }


        public CourseDetails GetCourseDetailsById(int courseId)
        {
            var parameters = new Collection<SqlParameter> {
                                                               new SqlParameter("@CourseId",courseId),
                                                          };

            
            if (!string.IsNullOrEmpty(ProcedureConfiguration.CourseDetailsByCourseId))
            {
                var courseDetails = new CourseDetails();
                var courseExist = false;
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.CourseDetailsByCourseId, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result.Read())
                    {
                        courseExist = true;
                        var courseInfomation = new CourseInfomation
                        {
                            CourseId = result["CourseId"].ToInt32OrDefault()
                        };
                        var courseFeedback = new CourseFeedBack
                        {
                            CourseId = result["CourseId"].ToInt32OrDefault()
                            
                        };
                        var courseInstructor = new CourseInstructor
                        {
                            CourseId = result["CourseId"].ToInt32OrDefault()
                        };

                        courseDetails.CourseId = result["CourseId"].ToInt32OrDefault();
                        courseDetails.CourseName = result["CourseName"].ToStringOrDefault();
                        courseDetails.CourseCategoryId = result["CourseCategoryId"].ToInt32OrDefault();
                        courseDetails.CourseCategory = result["CourseCategory"].ToStringOrDefault();
                        courseDetails.CourseCategoryName = result["CourseCategoryName"].ToStringOrDefault();
                        courseDetails.CourseYear = result["CourseYear"].ToInt32OrDefault();
                        courseDetails.Costs = (decimal?) result["Costs"];
                        courseDetails.Currency = result["CurrencyName"].ToStringOrDefault();
                        courseDetails.TimeCommit = result["TimeCommit"].ToInt32OrDefault();
                        courseDetails.TrainedDuration = (decimal?) result["TrainedDuration"];
                        courseDetails.Note = result["Note"].ToStringOrDefault();
                        courseDetails.EndDate = result["EndDate"].ToDateTimeOrDefault();
                        courseDetails.StartDate = result["StartDate"].ToDateTimeOrDefault();
                        courseDetails.TotalRecord = result["TotalRow"].ToInt32OrDefault();
                    }
                }

                if (courseExist)
                {
                   

                    // Get all skills
                    courseDetails.CourseInfomations =new CourseInformationEmployeeRepository().AllEmployeeByCourseId(courseId);

                    // Get all certificates
                    courseDetails.CourseFeedBacks = new CourseFeedBackRepository().CourseFeedBacks(courseId);

                    // Get all courses
                    courseDetails.CourseInstructors = new CourseInstructorRepository().CourseInstructorsByCourseId(courseId);
                }

                return courseDetails;
            }
            return default(CourseDetails);
        }
        
        #endregion
    }
}